package lv.solcraft.replication.client;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class OperExecutorSqlHolder extends JdbcDaoSupport {

    private Map<String, String> currRecordStatements;

    private Map<String, String> execProcStatements;

    private Map<String, List<String>> execProcParams;

    private Map<String, List<String>> tableFields;

    public OperExecutorSqlHolder() {
        this.currRecordStatements = new HashMap<String, String>();
        this.execProcStatements = new HashMap<String, String>();
        this.execProcParams = new HashMap<String, List<String>>();
        this.tableFields = new HashMap<String, List<String>>();
    }

    public String getCurrRecordStatement(String tableName) throws DataAccessException {

        String sql = (String) currRecordStatements.get(tableName);

        if (sql == null) {
            sql = createCurrRecordStatement(tableName);
            currRecordStatements.put(tableName, sql);
        }

        return sql;
    }

    public String getStoredProcStatement(String tableName, String operType) {

        String procName = getProcName(tableName, operType);
        String sql = (String) execProcStatements.get(procName);

        if (sql == null) {
            sql = createStoredProcSql(procName);
            execProcStatements.put(procName, sql);
        }

        return sql;
    }

    public List<String> getStoredProcParams(String tableName, String operType) {
        String procName = getProcName(tableName, operType);
        return getStoredProcParams(procName);
    }

    private List<String> getStoredProcParams(String procName) {
        List<String> list = (List<String>) execProcParams.get(procName);
        if (list == null) {
            list = createStoredProcParams(procName);
            execProcParams.put(procName, list);
        }

        return list;
    }

    private String createCurrRecordStatement(String tableName) {

        List<String> fields = getJdbcTemplate().queryForList("SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS " + "WHERE RDB$RELATION_NAME = ?", new String[] { tableName }, String.class);

        String fieldList = "";

        for (String tmp : fields) {
            String field = tmp.trim();
            if (!fieldList.equals("")) {
                fieldList = fieldList + ", ";
            }
            fieldList = fieldList + field;
        }

        String sql = "SELECT " + fieldList + " FROM " + tableName + " WHERE SCR_SITE_ID = ? AND SCR_RECORD_ID = ?";
        return sql;
    }

    public List<String> getTableFields(String tableName) {
        List<String> fields = tableFields.get(tableName);
        if (fields == null) {
            fields = loadTableFields(tableName);
            tableFields.put(tableName, fields);
        }
        return fields;
    }

    private List<String> loadTableFields(String tableName) {
        List<String> fields = getJdbcTemplate().queryForList("SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS " + "WHERE RDB$RELATION_NAME = ?", new String[] { tableName }, String.class);
        for (int i = 0; i < fields.size(); i++) {
            fields.set(i, fields.get(i).trim());
        }
        return fields;
    }

    public boolean isFieldInTable(String tableName, String field) {
        List<String> flds = getTableFields(tableName);
        return flds.indexOf(field) >= 0;
    }

    private String createStoredProcSql(String procName) throws DataAccessException {

        String sql = "SELECT SCR_ERR_CODE, SCR_ERR_MESSAGE FROM " + procName + "(";

        List paramList = getStoredProcParams(procName);
        int paramCount = paramList.size();

        for (int i = 0; i < paramCount; i++) {
            if (i > 0)
                sql = sql + ", ";
            sql = sql + "?";
        }
        sql = sql + ")";

        return sql;
    }

    private List<String> createStoredProcParams(String procName) {

        List<String> tmp = getJdbcTemplate().queryForList(
                "SELECT RDB$PARAMETER_NAME" + " FROM RDB$PROCEDURE_PARAMETERS WHERE RDB$PROCEDURE_NAME = ?" + " AND RDB$PARAMETER_TYPE = 0" + " ORDER BY RDB$PARAMETER_NUMBER",
                new String[] { procName }, String.class);

        List<String> params = new ArrayList<String>(tmp.size());

        for (Iterator it = tmp.iterator(); it.hasNext();) {
            String param = (String) it.next();
            params.add(param.trim());
        }

        return params;
    }

    private String getProcName(String tableName, String operType) {
        return "SCR_" + tableName.trim() + "_" + operType;
    }

}
